﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Dosimetria.DAL
{
    public sealed class Conexion
    {
        private string urlConexion;
        private static readonly Conexion instance = new Conexion();
        private static SqlConnection conn;


        private Conexion()
        {
            //local
            urlConexion = "data source = 192.168.75.128; initial catalog = dosimetria_pruebas; user id = sa; password = computador";
            
            //Interno CCHEN
            //urlConexion = "data source = 200.28.104.222; initial catalog = dosimetria_pruebas; user id = usuDosimetria; password = paso";
            conn = new SqlConnection(urlConexion);
        }

        public static Conexion Instance()
        {
            return instance;
        }

        public static void deleteData(string query, params object[] argsVars)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query, conn);

                List<string> lParameters = getNameOfParameters(query);
                int i = 0;
                foreach (object o in argsVars)
                {
                    SqlParameter parameter = new SqlParameter(lParameters.ElementAt(i), o);
                    cmd.Parameters.Add(parameter);
                    i++;
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
        }

        public static int updateData(string query, params object[] argsVars)
        {
            int affectedRows = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query, conn);

                List<string> lParameters = getNameOfParameters(query);
                int i = 0;
                foreach (object o in argsVars)
                {
                    SqlParameter parameter = new SqlParameter(lParameters.ElementAt(i), o);
                    cmd.Parameters.Add(parameter);
                    i++;
                }
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                
            }
            finally
            {
                conn.Close();
            }
            return affectedRows;
        }

        /// <summary>
        /// Inserta un nuevo registro y devuelve el valor del id insertado (Siempre y cuando exista una columna identity)
        /// </summary>
        /// <param name="query"></param>
        /// <param name="argsVars"></param>
        /// <returns></returns>
        public static int insertData(string query, params object[] argsVars)
        {
            int newID = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query, conn);

                List<string> lParameters = getNameOfParameters(query);

                int i = 0;
                foreach (object o in argsVars)
                {
                    SqlParameter parameter = new SqlParameter(lParameters.ElementAt(i), o);
                    cmd.Parameters.Add(parameter);
                    i++;
                }
                 cmd.ExecuteScalar();
           
            }
            catch(Exception e)
            {
                
            }
            finally
            {
                conn.Close();
            }
            return 0;
        }

        //Usar argumentos variables
        public static DataTable getSelectTable(string query, params object[] argsVars)
        {
            //DataSet data = new DataSet();

            DataTable dt = new DataTable();
            try
            {
                conn.Open();

                SqlDataAdapter sqlComm = new SqlDataAdapter(query, conn);

                List<string> lParameters = getNameOfParameters(query);

                int i = 0;
                foreach (object o in argsVars)
                {
                    sqlComm.SelectCommand.Parameters.Add(new SqlParameter(lParameters.ElementAt(i), o));
                    i++;
                }
                sqlComm.Fill(dt);
            }
            catch (Exception e)
            {

            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

        public static int getLastIndexInsertedInIdentityByNameOfTable(string nameTable)
        {
            int identity = 0;
            try
            {
                DataTable dt = new DataTable();
                conn.Open();
                string query = "SELECT IDENT_CURRENT('" + nameTable + "') as id";
                SqlDataAdapter sqlComm = new SqlDataAdapter(query, conn);
                sqlComm.Fill(dt);

                foreach (DataRow f in dt.Rows)
                {
                    identity = Convert.ToInt32(f["id"]);
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                conn.Close();
            }
            return identity;
        }

        /// <summary>
        /// Metodo que retorna una lista de los parametros de la query parametrizada
        /// </summary>
        /// <param name="query">Query a obtener los parametros</param>
        /// <returns></returns>
        private static List<string> getNameOfParameters(string query)
        {
            query = query.Trim();
            List<string> lParameters = new List<string>();
            char[] sArray = query.ToCharArray();
            int contador = 0;

            do
            {
                if (sArray[contador].Equals('@'))
                {

                    char[] aux = getSubCharArray(sArray, contador);
                    int i = 0;
                    string parametro = "";
                    do
                    {
                        if (aux[i] == ' ' || aux[i] == ',' || aux[i] == ')' || aux[i] == '%' )
                        {
                            i = aux.Length;
                        }
                        else
                        {
                            parametro += aux[i];
                        }
                        i++;
                    } while (i < aux.Length);
                    lParameters.Add(parametro);
                }
                contador++;
            } while (contador < sArray.Length);
            return lParameters;
        }

        /// <summary>
        /// Metodo que retorna un sub array de char desde cierta posicion
        /// </summary>
        /// <param name="s">Array original</param>
        /// <param name="pos">Posicion desde donde se quiere retornar el subarray</param>
        /// <returns></returns>
        private static char[] getSubCharArray(char[] s, int pos)
        {
            char[] subArray = new char[s.Length - pos];
            int contador = 0;
            for (int i = pos; i < s.Length; i++)
            {
                subArray[contador] = s[i];
                contador++;
            }
            return subArray;
        }

    }
}